# -*- coding: utf-8 -*-
"""
Created on Tue Jan 12 09:39:47 2021

@author: gcq
"""

import pandas as pd
from urllib.parse import quote
import requests
import json
import pymysql
from sqlalchemy import create_engine


class FillDistrict(object):
    """
    """
    errorCount = 0;
    def amap_lnglat(self, target):
        url_str="https://restapi.amap.com/v3/config/district?"
        key='7aee39e5e143265d0359077beb64d705'
        #取adcode
        keywords=target[1]
        url=url_str+"key="+key+"&keywords="+str(keywords)+"&subdistrict=0&extensions=all"
        res=requests.get(url).json()
        size = len(res['districts'])
        if size > 0 :
            res=res['districts'][0]
            lng,lat=res['center'].split(",")
            return lng,lat,res['polyline']
        else :
            print(target[0], target[1])
            self.errorCount += 1;
            return 0, 0, ''

    def read_lnglat(self):
        city_list=pd.read_excel("AMap_adcode_citycode2.xlsx", sheet_name=0)
        return city_list

    def fill_lnglat(self):
        city_list=pd.read_excel("AMap_adcode_citycode.xlsx", sheet_name=0)
        print("len=", len(city_list))
        for i in range(len(city_list)):
            try:
                city_list.iloc[i,3],city_list.iloc[i,4],city_list.iloc[i,5]=self.amap_lnglat(city_list.iloc[i])
                #lng,lat,polyline=amap_lnglat(city_list.iloc[i])
                #break
            except Exception as e:
                print(city_list.iloc[i,0],city_list.iloc[i,1],city_list.iloc[i,2])
                print (e)
        print("@@@errorCount:", self.errorCount)
        try:
            city_list.to_excel("AMap_adcode_citycode2.xlsx", sheet_name="Sheet1", encoding='utf-8')
        except Exception as er:
            print(er)
        return city_list

    def create_table(self):
        #创建表#
        conn=pymysql.connect('172.28.56.90','mysql','egSQ7HhxajHZjvdX','dw')
        cursor=conn.cursor()
        sql="""CREATE TABLE `dim_amap` (
          `fullname` varchar(50) DEFAULT NULL COMMENT '城市短名称，如北京',
          `adcode` bigint(20) UNIQUE NOT NULL COMMENT '城市id',
          `citycode` varchar(10) DEFAULT NULL COMMENT '城市编号',
          `lng` decimal(10,7) DEFAULT NULL COMMENT '经度',
          `lat` decimal(10,7) DEFAULT NULL COMMENT '纬度',
          `boundary` longtext COMMENT '区县坐标边界',
          `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
          `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
          PRIMARY KEY (`adcode`)
        ) ENGINE=InnoDB AUTO_INCREMENT=610125 DEFAULT CHARSET=utf8mb4;"""
        cursor.execute(sql)


if __name__ == "__main__":
    obj = FillDistrict()
    #插入数据#
    engine=create_engine('mysql+pymysql://mysql:egSQ7HhxajHZjvdX@172.28.56.90:3306/dw?charset=utf8')
    conn=engine.connect()
    print(conn)
    #obj.create_table()
    city_list = obj.fill_lnglat() #read_lnglat()
    print(len(city_list))
    #city_list.to_sql('dim_amap',engine,if_exists='append',index = False,index_label = False)
    print(1111111111)
